package com.huidao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.huidao.entity.Goods;
import com.huidao.entity.User;
import com.huidao.util.JDBCUtil;

public class GoodsDaoImpl implements GoodsDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;

	@Override
	public List<Goods> getGoodsBySeller(int startnum, int pageSize, long userId) {
		List<Goods> list = new ArrayList<Goods>();
		String sql = "SELECT * FROM GOODS WHERE USER_ID=? LIMIT ?,?";
		try {
			conn = JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			ps.setLong(1, userId);
			ps.setInt(2, startnum);
			ps.setInt(3, pageSize);
			rs = ps.executeQuery();
			while(rs.next()){
				Goods g = new Goods();
				g.setGoodsId(rs.getLong("GOODS_ID"));
				g.setGoodsAccount(rs.getInt("GOODS_ACCOUNT"));
				g.setGoodsName(rs.getString("GOODS_NAME"));
				g.setGoodsPrice(Double.parseDouble(rs.getString("GOODS_PRICE")));
				g.setGoodsShelves(rs.getInt("GOODS_SHELVES"));
				list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}

	@Override
	public int getTotalCountBySeller(long userId) {
		String sql = "SELECT COUNT(*) FROM GOODS WHERE USER_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}

	@Override
	public boolean addGoods(Goods goods) {
		String sql = "INSERT INTO GOODS(GOODS_NAME,GOODS_PRICE,GOODS_ACCOUNT,GOODS_SHELVES,USER_ID)" +
				"VALUES(?,?,?,0,?)";
		try {
			conn =JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			ps.setString(1, goods.getGoodsName());
			ps.setDouble(2, goods.getGoodsPrice());
			ps.setInt(3, goods.getGoodsAccount());
			ps.setLong(4, goods.getUser().getUserId());
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean delGoods(long goodsId) {
		String sql = "DELETE FROM GOODS WHERE GOODS_ID=?" ;
		try {
			conn =JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			ps.setLong(1, goodsId);
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateGoods(Goods goods) {
		String sql = "UPDATE GOODS SET GOODS_NAME=?,GOODS_PRICE=?," +
				"GOODS_ACCOUNT=? WHERE GOODS_ID=?" ;
		try {
			conn =JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			ps.setString(1, goods.getGoodsName());
			ps.setDouble(2, goods.getGoodsPrice());
			ps.setInt(3, goods.getGoodsAccount());
			ps.setLong(4, goods.getGoodsId());
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean shelvesGoods(long goodsId, int goodsShelves) {
		String sql = "UPDATE GOODS SET GOODS_SHELVES=? WHERE GOODS_ID=?" ;
		try {
			conn =JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			ps.setInt(1, goodsShelves);
			ps.setLong(2, goodsId);
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public List<Goods> getGoods(int startnum, int pageSize, String orderColumn) {
		List<Goods> list = new ArrayList<Goods>();
		String sql = "SELECT * FROM GOODS a " +
				"LEFT JOIN USER b ON a.USER_ID=b.USER_ID " +
				"WHERE a.GOODS_SHELVES=1 ORDER BY "+orderColumn+
				" LIMIT ?,? ";
		try {
			conn = JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			ps.setInt(1, startnum);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while(rs.next()){
				Goods g = new Goods();
				g.setGoodsId(rs.getLong("GOODS_ID"));
				g.setGoodsName(rs.getString("GOODS_NAME"));
				g.setGoodsPrice(rs.getFloat("GOODS_PRICE"));
				g.setGoodsAccount(rs.getInt("GOODS_ACCOUNT"));
				User user = new User();
				user.setUserNickname(rs.getString("USER_NICKNAME"));
				user.setUserPhone(Long.parseLong(rs.getString("USER_PHONE")));
				user.setUserShopname(rs.getString("USER_SHOPNAME"));
				g.setUser(user);
				list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		
		return list;
	}

	@Override
	public int getTotalCount() {
String sql = "SELECT COUNT(*) FROM GOODS WHERE GOODS_SHELVES=1";
		
		try {
			conn = JDBCUtil.getConn();
			ps =conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}

	public boolean updateAccount(Connection conn, long goodsId, int num) {
		String sql = "UPDATE GOODS SET GOODS_ACCOUNT=GOODS_ACCOUNT-? WHERE GOODS_ID=? AND GOODS_ACCOUNT>=?";
		try {
			ps =conn.prepareStatement(sql);
			ps.setInt(1, num);
			ps.setLong(2, goodsId);
			ps.setInt(3, num);
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.free(null, ps, rs);

		}
		return false;
	}

}
